﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Reflection;
using System.Text;
using System.Text.RegularExpressions;

namespace DataAccessService.Repository
{
    public class MsSQLEntityRepository
    {
        /// <summary>
        /// 获取新增实体sql
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="item"></param>
        /// <param name="tableColumns">表格列集合</param>
        /// <param name="idFiled">id字段</param>
        /// <returns></returns>
        public string GetInsertSql<T>(T item, string idFiled,List<string> tableColumns)
        {
            StringBuilder sb = new StringBuilder();
            sb.Append("insert into ");
            sb.Append(typeof(T).Name + " (");

            T t = (T)Activator.CreateInstance(typeof(T));
            PropertyInfo[] propertys = t.GetType().GetProperties();

            string fileStr = string.Empty;
            string valueStr = string.Empty;

            foreach (PropertyInfo pi in propertys)
            {
                if (pi.Name.ToLower() == idFiled.ToLower())
                    continue;

                object value = pi.GetValue(item, null);
                if (value == null)
                    continue;

                Regex reg = new Regex("^[0-9]+$");
                Match mh = reg.Match(value.ToString());
                if (mh.Success && !Equals(value.GetType(), typeof(string)))
                {
                    valueStr += string.Format("{0},", value);
                }
                else if (Equals(value.GetType(), typeof(bool)))
                {
                    valueStr += string.Format("{0},", (bool)value ? "1" : "0");
                }
                else if (Equals(value.GetType(), typeof(DateTime)))
                {
                    valueStr += string.Format("'{0}',", Convert.ToDateTime(value).ToString("yyyy-MM-dd HH:mm:ss"));
                }
                else
                {
                    valueStr += string.Format("'{0}',", value.ToString().Replace("'", "''"));
                }
                fileStr += (pi.Name + ",");

            }
            if (!String.IsNullOrEmpty(fileStr))
            {
                fileStr = fileStr.Substring(0, fileStr.Length - 1);
                valueStr = valueStr.Substring(0, valueStr.Length - 1);
            }
            sb.Append(fileStr);
            sb.Append(" ) values (");
            sb.Append(valueStr);
            sb.Append(")");

            string sql = sb.ToString();

            return sql;
        }

        /// <summary>
        /// 获取更新语句
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="idField"></param>
        /// <param name="item"></param>
        /// <param name="id"></param>
        /// <param name="tableColumns">表格列集合</param>
        /// <returns></returns>
        public string GetUpdateSql<T>(T item, string idField, string id, List<string> tableColumns)
        {
            StringBuilder sb = new StringBuilder();
            sb.Append("UPDATE ");
            sb.Append(typeof(T).Name + " set ");

            T t = (T)Activator.CreateInstance(typeof(T));
            PropertyInfo[] propertys = t.GetType().GetProperties();

            foreach (PropertyInfo pi in propertys)
            {
                if (pi.Name.ToLower() == idField.ToLower())
                    continue;

                object value = pi.GetValue(item, null);

                if (value == null)
                    continue;

                Regex reg = new Regex("^[0-9]+$");
                Match mh = reg.Match(value.ToString());
                if (mh.Success && !Equals(value.GetType(), typeof(string)))
                {
                    sb.Append(string.Format("{0}={1},", pi.Name, value.ToString()));
                }
                else if (Equals(value.GetType(), typeof(bool)))
                {
                    sb.Append(string.Format("{0}={1},", pi.Name, (bool)value ? "1" : "0"));
                }
                else if (Equals(value.GetType(), typeof(DateTime)))
                {
                    sb.Append(string.Format("{0}='{1}',", pi.Name, Convert.ToDateTime(value).ToString("yyyy-MM-dd HH:mm:ss")));
                }
                else
                {
                    sb.Append(string.Format("{0}='{1}',", pi.Name, value.ToString()));
                }
            }
            string sql = sb.ToString().Substring(0, sb.ToString().Length - 1);
            sql += string.Format(" where " + idField + "=" + id);
            return sql;
        }


    }
}
